package org.mebusw.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.text.SimpleDateFormat;
import java.util.List;
import org.mebusw.dao.IUserDAO;
import org.mebusw.dbc.HibernateSessionFactory;
import org.mebusw.vo.User;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

/**
 * @see /mysql/createDB.sql
 * 
 * @author j1shen
 * 
 */
public class IUserDAOImpl implements IUserDAO {
	private Connection conn = null;

	public IUserDAOImpl(Connection conn) {
		this.conn = conn;
	}

	public boolean doCreate(User user) throws Exception {
		boolean flag = false;
		PreparedStatement pstmt = null;
		String sql;
		int currentMaxUid;

		try {
			sql = "SELECT MAX(uid) from user";
			pstmt = this.conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				currentMaxUid = rs.getInt(1);
				user.setUserId(currentMaxUid + 1);
			}

			sql = "INSERT INTO user(uid, username, password, score, reg_date, "
					+ "email, win_game_davinci, played_game_davinci, "
					+ "win_game_lc, played_game_lc, "
					+ "win_game_lotr, played_game_lotr) "
					+ "VALUES (?,?,PASSWORD(?),?,?,?,?,?,?,?,?,?)";
			pstmt = this.conn.prepareStatement(sql);
			pstmt.setInt(1, user.getUserId());
			pstmt.setString(2, user.getUsername());
			pstmt.setString(3, user.getPassword());
			pstmt.setInt(4, user.getScore());
			pstmt.setString(5, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
					.format(new Date()));
			pstmt.setString(6, user.getEmail());
			pstmt.setInt(7, user.getWin_game_davinci());
			pstmt.setInt(8, user.getPlayed_game_davinci());
			pstmt.setInt(9, user.getPlayed_game_lc());
			pstmt.setInt(10, user.getWin_game_lc());
			pstmt.setInt(11, user.getPlayed_game_lotr());
			pstmt.setInt(12, user.getWin_game_lotr());

			int count = pstmt.executeUpdate();
			if (count > 0)
				flag = true;
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				pstmt.close();
			} catch (Exception e) {

			}
		}
		return flag;
	}

	public boolean doDelete(int uid) throws Exception {
		boolean flag = false;
		PreparedStatement pstmt = null;
		String sql = "DELETE FROM user WHERE uid=?";

		try {
			pstmt = this.conn.prepareStatement(sql);
			pstmt.setInt(1, uid);
			int count = pstmt.executeUpdate();
			if (count > 0)
				flag = true;
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				pstmt.close();
			} catch (Exception e) {

			}
		}

		return flag;
	}

	public boolean doUpdate(User user) throws Exception {
		boolean flag = false;
		PreparedStatement pstmt = null;
		String sql = "UPDATE user SET username=?, password=password(?), score=?, email=?, "
				+ "win_game_davinci=?, played_game_davinci=?, win_game_lc=?, played_game_lc=?, "
				+ "win_game_lotr=?, played_game_lotr=? WHERE uid=?";

		try {
			pstmt = this.conn.prepareStatement(sql);
			pstmt.setString(1, user.getUsername());
			pstmt.setString(2, user.getPassword());
			pstmt.setInt(3, user.getScore());
			pstmt.setString(4, user.getEmail());
			pstmt.setInt(5, user.getWin_game_davinci());
			pstmt.setInt(6, user.getPlayed_game_davinci());
			pstmt.setInt(7, user.getWin_game_lc());
			pstmt.setInt(8, user.getPlayed_game_lc());
			pstmt.setInt(9, user.getWin_game_lotr());
			pstmt.setInt(10, user.getPlayed_game_lotr());
			pstmt.setInt(11, user.getUserId());

			int count = pstmt.executeUpdate();
			if (count > 0)
				flag = true;
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				pstmt.close();
			} catch (Exception e) {

			}
		}

		return flag;
	}

	public List<User> findAll(int currentPage, int lineSize, String keyword)
			throws Exception {
		List<User> allUsers = new ArrayList<User>();
		PreparedStatement pstmt = null;
		String sql;

		try {
			/*
			 * //for Oracle statement String sql = "SELECT temp.* FROM " +
			 * "(SELECT uid, username, password, score, reg_date, ROWNUM rn " +
			 * "FROM user " +
			 * "WHERE (uid LIKE upper(?) OR username LIKE upper(?) OR score LIKE upper(?) "
			 * +
			 * "OR to_char(reg_date, 'yyyy') LIKE upper(?) OR to_char(reg_date, 'mm') LIKE upper(?) "
			 * + "OR to_char(reg_date, 'dd') LIKE upper(?)) " //end of WHERE +
			 * "AND ROWNUM <= " + (currPage * pageSize) + ")" //end of 2nd
			 * SELECT + "temp WHERE temp.rn >" + (currPage - 1) * pageSize;
			 */
			// for MySQL statement
			sql = "SET @cnt = 0";
			pstmt = this.conn.prepareStatement(sql);
			pstmt.executeQuery();

			sql = "select * from ("
					+ "select (@cnt := @cnt + 1) as rn, uid, username, password, score, reg_date, "
					+ "win_game_davinci, played_game_davinci, win_game_lc, played_game_lc, "
					+ "win_game_lotr, played_game_lotr "
					+ "from user "
					+ "where uid like upper(?) or username like upper(?) or score like upper(?) "
					+ ") as temp where " + " temp.rn <= "
					+ (currentPage * lineSize) + " and temp.rn > "
					+ ((currentPage - 1) * lineSize);

			pstmt = this.conn.prepareStatement(sql);
			pstmt.setString(1, "%" + keyword + "%");
			pstmt.setString(2, "%" + keyword + "%");
			pstmt.setString(3, "%" + keyword + "%");

			ResultSet rs = pstmt.executeQuery();

			User user = null;
			// int rn;
			while (rs.next()) {
				user = new User();
				// rn = rs.getInt(1);
				user.setUserId(rs.getInt(2));
				user.setUsername(rs.getString(3));
				user.setPassword(rs.getString(4));
				user.setScore(rs.getInt(5));
				user.setReg_date(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
						.parse(rs.getString(6)));
				user.setWin_game_davinci(rs.getInt(7));
				user.setPlayed_game_davinci(rs.getInt(8));
				user.setWin_game_lc(rs.getInt(9));
				user.setPlayed_game_lc(rs.getInt(10));
				user.setWin_game_lotr(rs.getInt(11));
				user.setPlayed_game_lotr(rs.getInt(12));

				allUsers.add(user);
			}
			rs.close();
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				pstmt.close();
			} catch (Exception e) {
			}
		}
		return allUsers;
	}

	public User findById(int uid) throws Exception {
		User user = null;
		PreparedStatement pstmt = null;
		String sql = "SELECT uid, username, password, score, "
				+ "reg_date, email, win_game_davinci, played_game_davinci, "
				+ "win_game_lc, played_game_lc, "
				+ "win_game_lotr, played_game_lotr " + "FROM user WHERE uid=?";

		try {
			pstmt = this.conn.prepareStatement(sql);
			pstmt.setInt(1, uid);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				user = new User();
				user.setUserId(rs.getInt(1));
				user.setUsername(rs.getString(2));
				user.setPassword(rs.getString(3));
				user.setScore(rs.getInt(4));
				user.setReg_date(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
						.parse(rs.getString(5)));
				user.setEmail(rs.getString(6));
				user.setWin_game_davinci(rs.getInt(7));
				user.setPlayed_game_davinci(rs.getInt(8));
				user.setWin_game_lc(rs.getInt(9));
				user.setPlayed_game_lc(rs.getInt(10));
				user.setWin_game_lotr(rs.getInt(11));
				user.setPlayed_game_lotr(rs.getInt(12));

			}
			rs.close();
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				pstmt.close();
			} catch (Exception e) {
			}
		}
		return user;
	}

	public int getAllCount(String keyword) throws Exception {
		PreparedStatement pstmt = null;
		String sql;
		int count = 0;

		try {
			sql = "select COUNT(uid) from user "
					+ "where uid like upper(?) or username like upper(?) or score like upper(?) ";

			pstmt = this.conn.prepareStatement(sql);
			pstmt.setString(1, "%" + keyword + "%");
			pstmt.setString(2, "%" + keyword + "%");
			pstmt.setString(3, "%" + keyword + "%");

			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {
				count = rs.getInt(1);
			}
			rs.close();
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				pstmt.close();
			} catch (Exception e) {
			}
		}
		return count;
	}

	public User findByName(String username) throws Exception {
		User user = null;
		PreparedStatement pstmt = null;
		String sql = "SELECT uid, username, password, score, "
				+ "reg_date, email, win_game_davinci, played_game_davinci, "
				+ "win_game_lc, played_game_lc, "
				+ "win_game_lotr, played_game_lotr "
				+ "FROM user WHERE username=?";

		try {
			pstmt = this.conn.prepareStatement(sql);
			pstmt.setString(1, username);
			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {
				user = new User();
				user.setUserId(rs.getInt(1));
				user.setUsername(rs.getString(2));
				user.setPassword(rs.getString(3));
				user.setScore(rs.getInt(4));
				user.setReg_date(rs.getDate(5));
				user.setEmail(rs.getString(6));
				user.setWin_game_davinci(rs.getInt(7));
				user.setPlayed_game_davinci(rs.getInt(8));
				user.setWin_game_lc(rs.getInt(9));
				user.setPlayed_game_lc(rs.getInt(10));
				user.setWin_game_lotr(rs.getInt(11));
				user.setPlayed_game_lotr(rs.getInt(12));
			}
			rs.close();
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				pstmt.close();
			} catch (Exception e) {
			}
		}
		return user;
	}

	public User findByEmail(String email) throws Exception {
		User user = null;
		PreparedStatement pstmt = null;
		String sql = "SELECT uid, username, password, score, "
				+ "reg_date, email, win_game_davinci, played_game_davinci, "
				+ "win_game_lc, played_game_lc, "
				+ "win_game_lotr, played_game_lotr "
				+ "FROM user WHERE email=?";

		try {
			pstmt = this.conn.prepareStatement(sql);
			pstmt.setString(1, email);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				user = new User();
				user.setUserId(rs.getInt(1));
				user.setUsername(rs.getString(2));
				user.setPassword(rs.getString(3));
				user.setScore(rs.getInt(4));
				user.setReg_date(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
						.parse(rs.getString(5)));
				user.setEmail(rs.getString(6));
				user.setWin_game_davinci(rs.getInt(7));
				user.setPlayed_game_davinci(rs.getInt(8));
				user.setWin_game_lc(rs.getInt(9));
				user.setPlayed_game_lc(rs.getInt(10));
				user.setWin_game_lotr(rs.getInt(11));
				user.setPlayed_game_lotr(rs.getInt(12));
			}
			rs.close();
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				pstmt.close();
			} catch (Exception e) {
			}
		}
		return user;
	}

	public static void main(String args[]) {
		Transaction tran = null;
		Session session = HibernateSessionFactory.getSession();
		try {
			// tran = session.beginTransaction();
			// User user = new User();
			// user.setUserId(100);
			// user.setUsername("icewe");
			// user.setPassword("123");
			// session.save(user);
			// tran.commit();

			/**
			 * HQL is different from SQL, in below, it's not "from user" (the
			 * table name) but "from User" (the POJO class name)
			 */
			Query query = session.createQuery("from User where uid<3");
			List<User> list = query.list();
			System.out.println(list.size());
		} catch (HibernateException e) {
			e.printStackTrace();
			if (tran != null) {
				try {
					tran.rollback();
				} catch (HibernateException tranEx) {
					tranEx.printStackTrace();
				}
			}
		}
		HibernateSessionFactory.closeSession();
	}

}
